package com.aadata.mds.biz.utils.sql;

import com.aadata.mds.core.util.FileWithExcelUtil;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import org.apache.http.entity.ContentType;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.util.*;
import java.util.stream.Collectors;
//<dependency>
//<groupId>org.apache.httpcomponents</groupId>
//<artifactId>httpcore</artifactId>
//<version>4.4.9</version>
//</dependency>
//<dependency>
//<groupId>org.springframework</groupId>
//<artifactId>spring-test</artifactId>
//<version>5.1.6.RELEASE</version>
//</dependency>
//<dependency>
//<groupId>cn.afterturn</groupId>
//<artifactId>easypoi-base</artifactId>
//<version>${easypoi-base.vaesion}</version>
//</dependency>
//
//<dependency>
//<groupId>cn.afterturn</groupId>
//<artifactId>easypoi-web</artifactId>
//<version>${easypoi-web.version}</version>
//</dependency>
//
//<dependency>
//<groupId>cn.afterturn</groupId>
//<artifactId>easypoi-annotation</artifactId>
//<version>${easypoi-annotation.version}</version>
//</dependency>
/**
 * @authur wsx
 * @date 2020/7/21 17:04
 */
public class ReadExcel {

    public static void main(String[] args) {

        File file = new File("E:\\test\\excel");
        StringBuilder str = new StringBuilder();
        StringBuilder errorStr = new StringBuilder();
        if (file.isDirectory()) {
            File[] files = file.listFiles();
            for (File excel : files) {
                try {
                    Map<Integer, String> map = null;
                    FileInputStream fileInputStream = new FileInputStream(excel);
                    MultipartFile multipartFile = new MockMultipartFile(excel.getName(), excel.getName(), ContentType.APPLICATION_OCTET_STREAM.toString(), fileInputStream);
                    map = getCreateTableSql(FileWithExcelUtil.importExcel(multipartFile, 0, 1, Field.class), excel.getName());
                    if (map.containsKey(0)) {
                        errorStr.append(map.get(0)).append("\r\n");
                    } else {
                        str.append(map.get(1)).append("\r\n");
                    }

                } catch (IOException e) {
                    e.printStackTrace();
                }

            }
        }
        //写入文件
        FileWriter fw = null;
        try {
            File sqlfile = new File("E:\\test\\init.sql");
            if (sqlfile.exists()) {
                sqlfile.delete();
            } else {
                sqlfile.createNewFile();
            }
            //创建字符输出流对象，负责向文件内写入
            fw = new FileWriter("E:\\test\\init.sql");
            //将str里面的内容读取到fw所指定的文件中
            fw.write(errorStr.toString() + str.toString());
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fw != null) {
                try {
                    fw.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

    }
    //处理excel数据
    public static Map<Integer, String> getCreateTableSql(List<Field> fieldList, String excelName) {
        Map map = new HashMap();
        StringBuilder str = new StringBuilder();
        String errorCode = "";
        int count = 0;

        //是否解析错误
        for (Field f : fieldList) {
            if (f.getColumnCode() == null) count++;
        }
        if (count == fieldList.size()) {
            str.append("-- **************************************").append("\r\n");
            str.append("-- ERROR:" + excelName + "解析失败！").append("\r\n");
            str.append("-- **************************************").append("\r\n");
            map.put(0, str.toString());
            return map;
        }
        //检验字段名称是否符合规范
        errorCode=checkColumnName(fieldList,errorCode);
        if (StringUtils.isNotEmpty(errorCode)) {
            str.append("-- **************************************").append("\r\n");
            str.append("-- ERROR:" + excelName + "  字段名 格式错误(数字开头或空格或其他):" + errorCode).append("\r\n");
            str.append("-- **************************************").append("\r\n");
            map.put(0, str.toString());
            return map;
        }

        //检查字段是否有重复
        List<String> repeatList = fieldList.stream().filter(ff -> null != ff.getColumnCode()).
                collect(Collectors.groupingBy(f -> f.getColumnCode(), Collectors.counting()))
                .entrySet().stream()
                .filter(entry -> entry.getValue() > 1)
                .map(entry -> entry.getKey())
                .collect(Collectors.toList());

        if (CollectionUtils.isNotEmpty(repeatList)) {
            str.append("-- **************************************").append("\r\n");
            str.append("-- ERROR:" + excelName + "  重复的字段值为:" + repeatList.toString()).append("\r\n");
            str.append("-- **************************************").append("\r\n");
            map.put(0, str.toString());
            return map;
        }

        String tableName = getStrContainData(excelName);

        str.append("----------------------------------------").append("\r\n");
        str.append("-- 创建  " + excelName).append("\r\n");
        str.append("----------------------------------------").append("\r\n");
        str.append("CREATE TABLE " + tableName + " ( ").append("\r\n");
        for (int i = 0; i < fieldList.size(); i++) {
            Field field = fieldList.get(i);
            String columnCode = field.getColumnCode();
            String columnType = field.getColumnType();
            String isNull = field.getIsNull();
            if (field != null && StringUtils.isNotEmpty(columnCode) && StringUtils.isNotEmpty(columnType)) {
                String s = "  " + columnCode + " "
                        + columnType.replaceAll("max|MAX", "4000");
                if (StringUtils.isNotEmpty(isNull) && ("必填".equals(isNull) || "Y".equals(isNull))) {
                    s += " " + "NOT NULL";
                } else {
                    s += " " + "NULL";
                }
                if (i == fieldList.size() - 1) {
                    str.append(s).append("\r\n");
                } else {
                    str.append(s).append(",").append("\r\n");
                }
            }
        }
        str.append(");").append("\r\n");

        str.append("----------------------------------------").append("\r\n");
        str.append("--------表注释------").append("\r\n");
        str.append("----------------------------------------").append("\r\n");

        String tableComment = excelName.replaceAll(".xls", "").replaceAll(".xlsx", "");
        str.append("COMMENT ON TABLE " + tableName + " IS '" + tableComment + "';").append("\r\n");

        str.append("----------------------------------------").append("\r\n");
        str.append("--------字段注释------").append("\r\n");
        str.append("----------------------------------------").append("\r\n");
        for (Field f : fieldList) {
            String remark = f.getRemark();
            String code = f.getColumnCode();
            String name = f.getColumnName();
            if (null != f && StringUtils.isNotEmpty(code)) {
                str.append("COMMENT ON COLUMN " + tableName + "." + code + " IS " + "'" + (StringUtils.isNotEmpty(name) ? name : "") + (StringUtils.isNotEmpty(remark) ? " " + remark : "") + "';").append("\r\n");
            }
        }
        str.append("----------------------------------------").append("\r\n");
        str.append("--------主键------").append("\r\n");
        str.append("----------------------------------------").append("\r\n");
        //处理主键
        int pkCount = 0;
        String pkcodes = "";
        for (Field f : fieldList) {
            String remark = f.getRemark();
            String code = f.getColumnCode();
            if (null != f && StringUtils.isNotEmpty(code) && StringUtils.isNotEmpty(remark) && -1 == remark.indexOf("复合") && -1 != remark.indexOf("主键")) {
                pkCount++;
                pkcodes += code + ",";
            }
        }
        if (pkCount == 1) {
            str.append("ALTER TABLE " + tableName + " ADD PRIMARY KEY (" + pkcodes.substring(0, pkcodes.length() - 1) + ");").append("\r\n");
        }
        if (pkCount > 1) {
            str.append("ALTER TABLE " + tableName + " ADD CONSTRAINT pk2_" + tableName + "  primary key (" + pkcodes.substring(0, pkcodes.length() - 1) + ");").append("\r\n");
        }
        //处理复合主键
        String codes = "";
        for (Field f : fieldList) {
            String remark = f.getRemark();
            String code = f.getColumnCode();
            if (null != f && StringUtils.isNotEmpty(code) && StringUtils.isNotEmpty(remark) && -1 != remark.indexOf("复合")) {
                codes += code + ",";
            }
        }
        if (StringUtils.isNotEmpty(codes)) {
            codes = codes.substring(0, codes.length() - 1);
            str.append("ALTER TABLE " + tableName + " ADD CONSTRAINT pk2_" + tableName + "  primary key (" + codes + ");").append("\r\n");
        }
        map.put(1, str.toString());
        return map;
    }
    //校验字段名称
    public static String checkColumnName(List<Field> fieldList,String errorCode){
         for (Field f : fieldList) {
             if (null != f) {
                 String columnCode = f.getColumnCode();
                 String columnType = f.getColumnType();
                 if (StringUtils.isNotEmpty(columnCode)) {
                     if (StringUtils.isEmpty(columnType)) {
                         errorCode += columnCode + ":数据类型为空！" + " * ";
                     }
                 }
                 if (StringUtils.isNotEmpty(columnType) && columnType.indexOf("(") != -1) {
                     if (columnType.indexOf(")") == -1) {
                         errorCode += columnCode + ":" + columnType + " 括号缺失" + " * ";
                     }
                 }
                 if (StringUtils.isNotEmpty(columnCode)) {
                     if (columnCode.indexOf(backchar(160)) != -1 || columnCode.indexOf(backchar(32)) != -1) {
                         errorCode += columnCode + " * ";
                     }
                     if (columnCode.indexOf(",") != -1 || columnCode.indexOf("(") != -1) {
                         errorCode += columnCode + " * ";
                     }
                     if (!isFirstAaZz(columnCode)) {
                         errorCode += columnCode + " * ";
                     }
                 }
             }
         }
        return errorCode;
     }
    //获取字符串括号内容（最后一个括号内）
    public static String getStrContainData(String tableName) {
        Stack st = new Stack();
        String str = "";
        ArrayList array = new ArrayList();
        for (char ch : tableName.toCharArray()) {
            if (ch != ')') {
                st.push(ch);
            } else {
                str = ")";
                while (true) {
                    char tmp = (Character) st.pop();
                    str = tmp + str;
                    if (tmp == '(') {
                        array.add(str);
                        str = "";
                        break;
                    }
                }
            }
        }
        String result = array.get(array.size() - 1).toString();
        return result.substring(result.indexOf("(") + 1, result.indexOf(")")).trim();
    }
    //判断首字母是否为字母
    public static boolean isFirstAaZz(String s) {
        char c = s.charAt(0);
        int i = (int) c;
        if ((i >= 65 && i <= 90) || (i >= 97 && i <= 122)) {
            return true;
        } else {
            return false;
        }
    }
    //根据ascII值获取字符
    public static char backchar(int backnum) {
        char strChar = (char) backnum;
        return strChar;
    }
}
